package sq.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import sq.dao.CountStatistics;
import sq.dao.OrdersItem;

import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author: sunquan
 * @Date: 2018/3/30
 * @Time: 14:11
 */
public interface OrdersItemRepository extends JpaRepository<OrdersItem,Integer> {
    List<OrdersItem> findAllByOrdersId(Integer ordersId);

    @Transactional
    @Modifying
    @Query(value = "select new sq.dao.CountStatistics(sum(o.totalPrice),o.createTime,sum(o.buyNumber)) from OrdersItem o where o.complete=:complete and o.createTime between :begin and :end group by DATE_FORMAT(o.createTime, '%Y-%m-%d')")
    List<CountStatistics> findAllByCompleteAndCreateTimeBetweenGroupByCreateTime(@Param("complete") boolean complete,
                                                                                 @Param("begin") Date begin,
                                                                                 @Param("end") Date end);

    @Transactional
    @Modifying
    @Query(value = "select new sq.dao.CountStatistics(o.foodName,sum(o.buyNumber)) from OrdersItem o  where o.complete=:complete and o.createTime between :begin and :end group by o.foodId order by sum(o.buyNumber) desc")
    List<CountStatistics> findTop(@Param("complete") boolean complete, @Param("begin") Date begin, @Param("end") Date end);

    @Transactional
    @Modifying
    @Query(value = "select new sq.dao.CountStatistics(o.foodName,o.foodId) from OrdersItem o  where o.complete=:complete group by o.foodId order by sum(o.buyNumber) desc")
    List<CountStatistics> findqtTop(@Param("complete") boolean complete);

}
